<?php

namespace App\Http\Controllers\Rank;

use App\Http\Controllers\BaseController;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class IndexController extends BaseController
{


    // 点赞最多的品牌
    public function brand(Request $request)
    {

        $param = $this->validate($request,[
            'firm_profession_id' => 'integer',
            'brand_location_id' => 'integer'
        ]);

        list($year,$month) = explode('-',date('Y-m'));


        $query = DB::table('user_like_brands')
            ->selectRaw('firm_brands.id,firm_brands.name,firm_brand_profiles.cover_one,firm_brand_profiles.cover_two,
                count(user_like_brands.brand_id) as cur_month_rank,firm_brands.last_month_like_rank')
            ->join('firm_brands','user_like_brands.brand_id','=','firm_brands.id')
            ->join('firm_brand_profiles','firm_brand_profiles.brand_id','=','firm_brands.id');

        if (isset($param['firm_profession_id'])){
            $query->where('firm_brands.firm_profession_id',$param['firm_profession_id']);
        }

        if (isset($param['brand_location_id'])){
            $query->where('firm_brands.brand_location_id',$param['brand_location_id']);
        }



        $ranks =  $query
            ->whereYear('user_like_brands.like_time',$year)
            ->whereMonth('user_like_brands.like_time',$month)
            ->where('user_like_brands.status',1)
            ->groupBy('user_like_brands.brand_id','firm_brands.name','firm_brand_profiles.cover_one','firm_brand_profiles.cover_two','firm_brands.last_month_like_rank')
            ->orderByDesc('cur_month_rank')
            ->get();


       return $this->success($ranks);

    }



    public function join(Request $request)
    {
        $param = $this->validate($request,[
            'firm_profession_id' => 'integer',
            'brand_location_id' => 'integer'
        ]);


        list($year,$month) = explode('-',date('Y-m'));

        $query =  DB::table('firm_brand_messages')
            ->selectRaw('firm_brands.id,firm_brands.name,firm_brand_profiles.cover_one,firm_brand_profiles.cover_two,
                count(firm_brand_messages.brand_id) as total,firm_brands.last_month_form_rank')
            ->join('firm_brands','firm_brand_messages.brand_id','=','firm_brands.id')
            ->join('firm_brand_profiles','firm_brand_profiles.brand_id','=','firm_brands.id');



        if (isset($param['firm_profession_id'])){
            $query->where('firm_brands.firm_profession_id',$param['firm_profession_id']);
        }

        if (isset($param['brand_location_id'])){
            $query->where('firm_brands.brand_location_id',$param['brand_location_id']);
        }


        $ranks = $query
            ->whereYear('firm_brand_messages.created_at',$year)
            ->whereMonth('firm_brand_messages.created_at',$month)
            ->groupBy('firm_brand_messages.brand_id','firm_brands.name','firm_brand_profiles.cover_one','firm_brand_profiles.cover_two','firm_brands.last_month_form_rank')
            ->orderByDesc('total')
            ->get();


        return $this->success($ranks);

    }





    public function preview(Request $request)
    {
        $param = $this->validate($request,[
            'firm_profession_id' => 'integer',
            'brand_location_id' => 'integer'
        ]);

        list($year,$month) = explode('-',date('Y-m'));

        $query = DB::table('firm_brand_browses')
            ->selectRaw('firm_brands.id,firm_brands.name,firm_brand_profiles.cover_one,firm_brand_profiles.cover_two,
                count(firm_brand_browses.brand_id) as total,firm_brands.last_month_view_rank')
            ->join('firm_brands','firm_brand_browses.brand_id','=','firm_brands.id')
            ->join('firm_brand_profiles','firm_brand_profiles.brand_id','=','firm_brands.id');

        if (isset($param['firm_profession_id'])){
            $query->where('firm_brands.firm_profession_id',$param['firm_profession_id']);
        }

        if (isset($param['brand_location_id'])){
            $query->where('firm_brands.brand_location_id',$param['brand_location_id']);
        }

        $ranks = $query->whereYear('firm_brand_browses.view_time',$year)
            ->whereMonth('firm_brand_browses.view_time',$month)
            ->groupBy('firm_brand_browses.brand_id','firm_brands.name','firm_brand_profiles.cover_one','firm_brand_profiles.cover_two','firm_brands.last_month_view_rank')
            ->orderByDesc('total')
            ->get();


        return $this->success($ranks);

    }





    // 管理公司表单留言量
    public function form(Request $request)
    {
        $param = $this->validate($request,[
            'firm_profession_id' => 'integer'
        ]);


        list($year,$month) = explode('-',date('Y-m'));
        $query =   DB::table('firm_messages')
            ->selectRaw('firms.id,firms.name,firm_profiles.cover_one,firm_profiles.cover_two,
                count(firm_messages.firm_id) as total,firms.last_month_form_rank')
            ->join('firms','firm_messages.firm_id','=','firms.id')
            ->join('firm_profiles','firm_profiles.firm_id','=','firms.id');

        if (isset($param['firm_profession_id'])){
            $query->where('firms.firm_profession_id',$param['firm_profession_id']);
        }



        $ranks = $query->whereYear('firm_messages.created_at',$year)
            ->whereMonth('firm_messages.created_at',$month)
            ->groupBy('firm_messages.firm_id','firms.name','firm_profiles.cover_one','firm_profiles.cover_two',
               'firms.last_month_form_rank')
            ->where('firms.category_id',2)
            ->orderByDesc('total')
            ->get();



        return $this->success($ranks);

    }


    // 观看量最多的管理公司

    public function view(Request $request)
    {
        $param = $this->validate($request,[
            'firm_profession_id' => 'integer'
        ]);

        list($year,$month) = explode('-',date('Y-m'));

        $query =      DB::table('firm_browses')
            ->selectRaw('firms.id,firms.name,firm_profiles.cover_one,firm_profiles.cover_two,
                count(firm_browses.firm_id) as total,firms.last_month_view_rank')
            ->join('firms','firm_browses.firm_id','=','firms.id')
            ->join('firm_profiles','firm_profiles.firm_id','=','firms.id')
            ->where('firms.category_id',2);

        if (isset($param['firm_profession_id'])){
            $query->where('firms.firm_profession_id',$param['firm_profession_id']);
        }

         $ranks = $query->whereYear('firm_browses.view_time',$year)
            ->whereMonth('firm_browses.view_time',$month)
             ->groupBy('firm_browses.firm_id','firms.name','firm_profiles.cover_one','firm_profiles.cover_two',
                 'firms.last_month_view_rank')
            ->orderByDesc('total')
            ->get();


        return $this->success($ranks);
    }

    // 观看量最高的课程
    public function course()
    {

        list($year,$month) = explode('-',date('Y-m'));
        $query =  DB::table('course_browses')
            ->join('courses','course_browses.course_id','=','courses.id')
            ->selectRaw('courses.name,courses.cover,course_browses.course_id,
            count(course_browses.course_id) as total,courses.last_month_view_rank');


        if (isset($param['firm_profession_id'])){
            $query->where('courses.firm_profession_id',$param['firm_profession_id']);
        }


        $ranks = $query->whereYear('course_browses.view_time',$year)
            ->whereMonth('course_browses.view_time',$month)
            ->groupBy('course_browses.course_id','courses.name','courses.cover','courses.last_month_view_rank')
            ->orderByDesc('total')
            ->get();



        return $this->success($ranks);

    }

    // 宝典收藏数量最高
    public function collect()
    {

        list($year,$month) = explode('-',date('Y-m'));


        $ranks =  DB::table('user_collect_articles')
            ->selectRaw('articles.title,articles.cover,user_collect_articles.article_id,count(user_collect_articles.article_id) as total,articles.last_month_book_rank')
            ->join('articles','user_collect_articles.article_id','=','articles.id')
            ->whereYear('user_collect_articles.collect_time',$year)
            ->whereMonth('user_collect_articles.collect_time',$month)
            ->groupBy('user_collect_articles.article_id','articles.title','articles.cover','articles.last_month_book_rank')
            ->where('articles.category_id',17)
            ->orderByDesc('total')
            ->get();

        return $this->success($ranks);

    }


    // 圈子点评数量最多的

    public function comments()
    {

        list($year,$month) = explode('-',date('Y-m'));

        $ranks = DB::table('article_comments')
            ->join('articles','article_comments.article_id','=','articles.id')
            ->join('circles','articles.circle_id','=','circles.id')
            ->selectRaw('circles.name,circles.logo,circles.area,circles.intro,articles.circle_id,
            count(articles.circle_id) as total,circles.last_month_comment_rank')
            ->whereYear('article_comments.created_at',$year)
            ->whereMonth('article_comments.created_at',$month)
            ->groupBy('articles.circle_id','circles.name','circles.logo','circles.area','circles.intro','circles.last_month_comment_rank')
            ->orderByDesc('total')
            ->get();

        return $this->success($ranks);
    }


    // 资讯观看量最高的

    public function post()
    {

        list($year,$month) = explode('-',date('Y-m'));
        $ranks = DB::table('article_browses')
            ->join('articles','article_browses.article_id','=','articles.id')
            ->selectRaw('articles.title,articles.cover,article_browses.article_id,count(article_browses.article_id) as total,articles.last_month_new_rank')
            ->whereYear('article_browses.view_time',$year)
            ->whereMonth('article_browses.view_time',$month)
            ->groupBy('article_browses.article_id','articles.title','articles.cover','articles.last_month_new_rank')
            ->where('articles.type',4)
            ->orderByDesc('total')
            ->get();

        return $this->success($ranks);

    }
}
